﻿using System.Data;
using System.Data.OleDb;

namespace VeriAlimi
{
    public class LinqToExcelProvider
    {
        /// <summary>
        /// Gets or sets the Excel filename
        /// </summary>
        private string FileName { get; set; }

        public LinqToExcelProvider(string fileName)
        {
            FileName = fileName;
        }

        /// <summary>
        /// Returns a worksheet as a linq-queryable enumeration
        /// </summary>
        /// <param name="sheetName">The name of the worksheet</param>
        /// <returns>An enumerable collection of the worksheet</returns>
        public EnumerableRowCollection<DataRow> GetWorkSheet(bool isOpenXMLFormat)
        {
            string connectionString = "";
            if (isOpenXMLFormat)
                //read a 2007 file
                connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Password=\"\";User ID=Admin;Data Source={0};Mode=Share Deny Write;Extended Properties=\"HDR=YES;\";Jet OLEDB:Engine Type=37", FileName);
            else
                //read a 97-2003 file
                connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", FileName);

            string sheetName = "";
            using (OleDbConnection cn = new OleDbConnection(connectionString))
            {
                cn.Open();
                System.Data.DataTable dataSet = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                sheetName = dataSet.Rows[0]["TABLE_NAME"].ToString();
                cn.Close();
            }

            // Query the specified worksheet
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", sheetName), connectionString);

            // Fill the dataset from the data adapter
            DataSet myDataSet = new DataSet();
            dataAdapter.Fill(myDataSet, "ExcelInfo");

            // Initialize a data table which we can use to enumerate the contents based on the dataset
            DataTable dataTable = myDataSet.Tables["ExcelInfo"];

            // Return the data table contents as a queryable enumeration
            return dataTable.AsEnumerable();
        }
    }
}
